﻿using System.Data.SqlClient;
using System.ComponentModel;
using System.Configuration;
using EasyMapping.Common.CustomException;
using System;
using EasyMapping.Common.Logging;
using System.Collections.Generic;
using System.Data.Common;

namespace EasyMapping.Common.Utility
{
    public class SQLHelper
    {
        #region " Private Fields "

        static string _connectionString = default(string);
        static Type _type = typeof(SQLHelper);

        #endregion

        #region " Common Functions "

        [EditorBrowsable(EditorBrowsableState.Always)]
        public static SqlConnection GetConnection()
        {
            SqlConnection connection = new SqlConnection();
            if (!string.IsNullOrEmpty(_connectionString))
            {
                connection.ConnectionString = _connectionString;
                return connection;
            }
            
            string key = string.Empty;
            key = ConfigurationManager.AppSettings["ConnectionStringKeyName"];
            ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[key];

            string connectionString = settings != null ? settings.ConnectionString : string.Empty ;
            if (string.IsNullOrEmpty(connectionString))
            {
                string message = "Could not find the connection string in config file";
                throw new ConfigurationNotFoundException(message, typeof(SQLHelper));
            }
            else
            {
                connection.ConnectionString = _connectionString =  connectionString;
            }
            return connection;
        } 

        #endregion

        #region " Actions "

        public static T GetSingleObject<T>(Func<DbCommand, DbDataReader> getReader, Func<DbDataReader, object> getObject)
        {
            SqlConnection connection = SQLHelper.GetConnection();
            object obj;
            try
            {
                connection.Open();
                SqlCommand cmd = connection.CreateCommand();
                SqlDataReader reader = (SqlDataReader)getReader(cmd);
                try
                {
                    while (reader.Read())
                    {
                        obj = getObject(reader);
                        return (T)obj;
                    }
                }
                catch (Exception e)
                {
                    ExceptionHandler.OnException(e, typeof(SQLHelper));
                }
                finally
                {
                    reader.Dispose();
                }
            }
            catch (Exception e)
            {
                ExceptionHandler.OnException(e, typeof(SQLHelper));
            }
            finally
            {
                connection.Dispose();
            }
            return default(T);
        }

        public static List<T> GetList<T>(Func<DbCommand, DbDataReader> getReader, Func<DbDataReader, object> getObject)
        {
            SqlConnection connection = SQLHelper.GetConnection();
            object obj;
            try
            {
                connection.Open();
                SqlCommand cmd = connection.CreateCommand();
                SqlDataReader reader = (SqlDataReader)getReader(cmd);
                try
                {
                    List<T> list = reader.HasRows ? new List<T>():default(List<T>);                   
                    while (reader.Read())
                    {                        
                        obj = getObject(reader);
                        list.Add((T)obj);                      
                    }
                    return list;
                }
                catch (Exception e)
                {
                    ExceptionHandler.OnException(e, typeof(SQLHelper));
                }
                finally
                {
                    reader.Dispose();
                }
            }
            catch (Exception e)
            {
                ExceptionHandler.OnException(e, typeof(SQLHelper));
            }
            finally
            {
                connection.Dispose();
            }
            return default(List<T>);
        }

        public static int ChangeObject<T>(Func<DbCommand, int> createObject)
        {
            SqlConnection connection = SQLHelper.GetConnection();
            try
            {
                connection.Open();
                SqlCommand cmd = (SqlCommand)connection.CreateCommand();
                int rows = createObject(cmd);
                return rows;
            }
            catch (Exception e)
            {
                ExceptionHandler.OnException(e, typeof(SQLHelper));
            }
            finally
            {
                connection.Dispose();
            }
            return -1;
        }

        #endregion 

        #region " Nested Class "

        private class ExceptionHandler
        { 
            internal static void OnException(Exception e, Type type)
            {
                string msg = ExceptionHelper.GetExceptionSource(type) + "\n" + e.ToString();
                Log4NetUtil.GetCommonLogger().Error(msg);
                msg = null;
                HandleException(e);
            }

            private static void HandleException(Exception e)
            {
                #if DEBUG
                    throw e;
                #endif
             }
        }

        #endregion
    }
}
